<?php
$db = new Database();
$fs = new Functions();
$set_date_time = $fs->set_default_timezone_datetime();
$auth = Auth::getAuth('current_user');
if(isset($_POST['action'])){
    $result = array();
    if($_POST['action'] == "load_all_users"){
        
        $select_from = $_POST['select_from'] + 1;
        $select_to = $_POST['select_to'];
        $tb_account = $db->tbl_name(TB_ACCOUNTS);
        $tb_users = $db->tbl_name(TB_USERS);
        $tb_roles = $db->tbl_name(TB_ROLES);
        $tb_brand_category = $db->tbl_name(TB_BRAND_CATEGORY);
//SELECT * FROM
//(SELECT CashAdvanceNo,EMpName, ROW_NUMBER() OVER(ORDER BY CashAdvanceNo) AS ROWID 
//FROM
//tbl_t_cashadvance CA
//LEFT JOIN tblemp EMP
//ON CA.RequestorNo = EMP.EmpNo) A
//WHERE A.ROWID>=1 AND A.ROWID <=10
        
        $str_Sql = "SELECT * FROM
                    (SELECT a.id as aID, a.email as aEmail, a.date_created as aDate_created,
                    a.date_modified as aDate_modified, a.is_active as aIs_active,
                    first_name, last_name, middle_name, department, a.brand_category as aBrand_category, 
                    
                    u.id as uID, u.email as uEmail, u.date_created as uDate_created,
                    u.date_modified as uDate_modified, u.is_active as uIs_active,
                    level_id,account_id,username,
                    
                    r.id as rRoleID, r.role_name as rRoleName, r.is_active as rIsActive,
                    
                    b.id as bBrandID, b.brand_category as bBrandCategory, b.is_active as bIsActive,
                    
                    ROW_NUMBER() OVER(ORDER BY a.id DESC) AS ROWID 
                    FROM
                    {$tb_account} a
                    LEFT JOIN {$tb_users} u ON a.id = u.account_id
                    LEFT JOIN {$tb_roles} r ON u.level_id = r.id
                    LEFT JOIN {$tb_brand_category} b ON a.brand_category = b.id
                    WHERE u.is_active = {$db->escape(1)}) A
                    WHERE A.ROWID>=$select_from AND A.ROWID <=$select_to
                    ";
        $load_users = $db->query($str_Sql,"array");
        $count_users = $db->query($str_Sql,"numrows");
        
        foreach($load_users as $data){
            
            $array = array("id" =>  $data['uID'],
            "title" =>  $data['first_name'] . " " . $data['last_name'],
            "width" =>  "50",
            "height" =>  "50",
            "class" =>  "img-polaroid",
            "style" =>  "",
            "size"  =>  "small");
            
            $img = $fs->avatarPic($array);
        
            $encrypted_user_id = $fs->base_encode_decode("encrypt", $data['uID']);
        
            $result[] = array("user"    =>  $data,
                              "others"  =>  array("img" => $img,
                                                   "encrypt_uID"    =>  $encrypted_user_id));
        }
        
        $ret = array("data"     =>  $result,
                    "count_users"   =>  $count_users,
                    "from"          =>  $select_from,
                    "to"            =>  $select_to,
                    "sql"=>$str_Sql);
        
        $string = json_encode($ret);
        
        echo Functions::view_json_formatter($string);
    }else if($_POST['action'] == "search_user"){
        $select_from = "0";
        $select_to = "10";
        $tb_account = $db->tbl_name(TB_ACCOUNTS);
        $tb_users = $db->tbl_name(TB_USERS);
        $tb_roles = $db->tbl_name(TB_ROLES);
        $tb_brand_category = $db->tbl_name(TB_BRAND_CATEGORY);
        
        $value = $_POST['search_val'];
        
        $str_Sql = "SELECT * FROM
                    (SELECT a.id as aID, a.email as aEmail, a.date_created as aDate_created,
                    a.date_modified as aDate_modified, a.is_active as aIs_active,
                    first_name, last_name, middle_name, department, a.brand_category as aBrand_category, 
                    
                    u.id as uID, u.email as uEmail, u.date_created as uDate_created,
                    u.date_modified as uDate_modified, u.is_active as uIs_active,
                    level_id,account_id,username,
                    
                    r.id as rRoleID, r.role_name as rRoleName, r.is_active as rIsActive,
                    
                    b.id as bBrandID, b.brand_category as bBrandCategory, b.is_active as bIsActive,
                    
                    ROW_NUMBER() OVER(ORDER BY a.id DESC) AS ROWID 
                    FROM
                    {$tb_account} a
                    LEFT JOIN {$tb_users} u ON a.id = u.account_id
                    LEFT JOIN {$tb_roles} r ON u.level_id = r.id
                    LEFT JOIN {$tb_brand_category} b ON a.brand_category = b.id
                    WHERE u.is_active = {$db->escape(1)} AND (a.first_name LIKE '%$value%' OR a.last_name LIKE '%$value%' OR r.role_name LIKE '%$value%'
                    OR b.brand_category LIKE '%$value%')) A
                    
                    ";
        $load_users = $db->query($str_Sql . " WHERE A.ROWID>=$select_from AND A.ROWID <=$select_to ","array");
        $count_users = $db->query($str_Sql,"numrows");
        
        foreach($load_users as $data){
            
            $array = array("id" =>  $data['uID'],
            "title" =>  $data['first_name'] . " " . $data['last_name'],
            "width" =>  "50",
            "height" =>  "50",
            "class" =>  "img-polaroid",
            "style" =>  "",
            "size"  =>  "small");
            
            $img = $fs->avatarPic($array);
        
            $encrypted_user_id = $fs->base_encode_decode("encrypt", $data['uID']);
        
            $result[] = array("user"    =>  $data,
                              "others"  =>  array("img" => $img,
                                                   "encrypt_uID"    =>  $encrypted_user_id));
            
            $fs->save_audit_logs($db,$auth['id'],"7","tb_users",$set_date_time['date_time'],$data['uID']);
        }
        
        $ret = array("data"     =>  $result,
                    "count_users"   =>  $count_users,
                    "from"          =>  $select_from,
                    "to"            =>  $select_to,
                    "sql"=>$str_Sql);
        
        $string = json_encode($ret);
        
        echo Functions::view_json_formatter($string);
        
        
    }else{
        $this->setLayout();
    }
}else{
    $this->setLayout();
}
?>